﻿using System.Drawing;
using ApiLib.AppStart;
using ApiLib.Utils;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using SqlSugar;

namespace ApiLib.Generator.Tools;

/// <summary>
/// 数据库结构生成Excel
/// </summary>
public class DbGenExcelTool
{
    /// <summary>
    /// 生成数据库结构到Excel
    /// </summary>
    /// <param name="scope"></param>
    /// <returns>流</returns>
    public static MemoryStream BuildDbExcel(SqlSugarScope scope)
    {
        var databaseInfos = GetDbInfos(scope);
        var workbook = new XSSFWorkbook();
        foreach (var databaseInfo in databaseInfos)
        {
            var sheet = workbook.CreateSheet(databaseInfo.ConfigId);
            var colIndex = 0;
            var rowIndex = 0;
            var row = sheet.CreateRow(rowIndex++);
            row.CreateCell(colIndex++).SetCellValue("表");
            row.CreateCell(colIndex++).SetCellValue("描述");
            row.CreateCell(colIndex++).SetCellValue("类型");
            row.CreateCell(colIndex++).SetCellValue("字段");
            row.CreateCell(colIndex++).SetCellValue("描述");
            foreach (var tableInfo in databaseInfo.TableInfos)
            {
                colIndex = 0;
                row = sheet.CreateRow(rowIndex++);
                row.CreateCell(colIndex++).SetCellValue(tableInfo.Name);
                row.CreateCell(colIndex++).SetCellValue(tableInfo.Description);
                row.CreateCell(colIndex++).SetCellValue(tableInfo.Type);
                for (var i = 0; i < tableInfo.ColumnInfos.Count; i++)
                {
                    if (i != 0)
                    {
                        colIndex = 0;
                        row = sheet.CreateRow(rowIndex++);
                        row.CreateCell(colIndex++).SetCellValue("");
                        row.CreateCell(colIndex++).SetCellValue("");
                        row.CreateCell(colIndex++).SetCellValue("");
                    }

                    row.CreateCell(colIndex++).SetCellValue(tableInfo.ColumnInfos[i].DbColumnName);
                    row.CreateCell(colIndex++).SetCellValue(tableInfo.ColumnInfos[i].ColumnDescription);
                }

                sheet.AddMergedRegion(new CellRangeAddress(rowIndex - tableInfo.ColumnInfos.Count, rowIndex - 1, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex - tableInfo.ColumnInfos.Count, rowIndex - 1, 1, 1));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex - tableInfo.ColumnInfos.Count, rowIndex - 1, 2, 2));
            }
        }

        return workbook.Write();
    }

    /// <summary>
    /// 生成数据库结构到Excel
    /// </summary>
    /// <param name="scope"></param>
    /// <returns>流</returns>
    public static MemoryStream BuildDbExcel2(SqlSugarScope scope)
    {
        var databaseInfos = GetDbInfos(scope);
        var workbook = new XSSFWorkbook();
        foreach (var databaseInfo in databaseInfos)
        {
            var sheet = workbook.CreateSheet(databaseInfo.ConfigId);
            var colIndex = 0;
            var rowIndex = 0;
            var row = sheet.CreateRow(rowIndex++);
            row.CreateCell(colIndex++).SetCellValue("字段或表");
            row.CreateCell(colIndex++).SetCellValue("类型");
            row.CreateCell(colIndex++).SetCellValue("描述");
            foreach (var tableInfo in databaseInfo.TableInfos)
            {
                colIndex = 0;
                row = sheet.CreateRow(rowIndex++);
                row.CreateCell(colIndex++).SetCellValue(tableInfo.Name);
                row.CreateCell(colIndex++).SetCellValue(tableInfo.Type);
                row.CreateCell(colIndex++).SetCellValue(tableInfo.Description);
                for (var i = 0; i < tableInfo.ColumnInfos.Count; i++)
                {
                    colIndex = 0;
                    row = sheet.CreateRow(rowIndex++);
                    row.CreateCell(colIndex++).SetCellValue(tableInfo.ColumnInfos[i].DbColumnName);
                    var dbType = tableInfo.ColumnInfos[i].DataType;
                    if (tableInfo.ColumnInfos[i].Length > 0)
                    {
                        dbType += "(" + tableInfo.ColumnInfos[i].Length + ")";
                    }

                    if (tableInfo.ColumnInfos[i].IsPrimarykey)
                    {
                        dbType += tableInfo.ColumnInfos[i].IsIdentity ? "[主键自增]" : "[主键]";
                    }

                    row.CreateCell(colIndex++).SetCellValue(dbType);
                    row.CreateCell(colIndex++).SetCellValue(tableInfo.ColumnInfos[i].ColumnDescription);
                }

                row = sheet.CreateRow(rowIndex++);
            }
        }

        return workbook.Write();
    }

    /// <summary>
    /// 生成数据库结构到Excel
    /// </summary>
    /// <param name="scope"></param>
    /// <returns>流</returns>
    public static MemoryStream BuildDbExcel3(SqlSugarScope scope)
    {
        var databaseInfos = GetDbInfos(scope);
        var workbook = new XSSFWorkbook();
        var cellStyle = GetCellHeaderStyle(workbook);
        foreach (var databaseInfo in databaseInfos)
        {
            var sheet = workbook.CreateSheet(databaseInfo.ConfigId);
            var rowIndex = 0;
            foreach (var tableInfo in databaseInfo.TableInfos)
            {
                var colIndex = 0;
                var row = sheet.CreateRow(rowIndex++);
                var cell = row.CreateCell(colIndex++);
                cell.CellStyle = cellStyle;
                cell.SetCellValue("表名");
                row.CreateCell(colIndex++).SetCellValue(tableInfo.Name);
                row.CreateCell(colIndex++).SetCellValue(tableInfo.Description);
                row = sheet.CreateRow(rowIndex++);
                colIndex = 0;
                cell = row.CreateCell(colIndex++);
                cell.CellStyle = cellStyle;
                cell.SetCellValue("列名");
                cell = row.CreateCell(colIndex++);
                cell.CellStyle = cellStyle;
                cell.SetCellValue("数据类型");
                cell = row.CreateCell(colIndex++);
                cell.CellStyle = cellStyle;
                cell.SetCellValue("说明");
                for (var i = 0; i < tableInfo.ColumnInfos.Count; i++)
                {
                    colIndex = 0;
                    row = sheet.CreateRow(rowIndex++);
                    row.CreateCell(colIndex++).SetCellValue(tableInfo.ColumnInfos[i].DbColumnName);
                    var dbType = tableInfo.ColumnInfos[i].DataType;
                    if (tableInfo.ColumnInfos[i].Length > 0)
                    {
                        dbType += "(" + tableInfo.ColumnInfos[i].Length + ")";
                    }

                    if (tableInfo.ColumnInfos[i].IsPrimarykey)
                    {
                        dbType += tableInfo.ColumnInfos[i].IsIdentity ? "[主键自增]" : "[主键]";
                    }

                    row.CreateCell(colIndex++).SetCellValue(dbType);
                    row.CreateCell(colIndex++).SetCellValue(tableInfo.ColumnInfos[i].ColumnDescription);
                }

                row = sheet.CreateRow(rowIndex++);
            }
        }

        return workbook.Write();
    }

    private static List<DbDatabaseInfo> GetDbInfos(SqlSugarScope scope)
    {
        var databaseInfos = new List<DbDatabaseInfo>();
        foreach (var config in GlobalConfig.ConnectionConfigs)
        {
            var connection = scope.GetConnection(config.ConfigId as string);
            var databaseInfo = new DbDatabaseInfo
            {
                ConfigId = config.GetDbName()
            };

            var tableInfoList = connection.DbMaintenance.GetTableInfoList();
            foreach (var tableInfo in tableInfoList)
            {
                var columnInfos = connection.DbMaintenance.GetColumnInfosByTableName(tableInfo.Name);
                databaseInfo.TableInfos.Add(new DbTableInfo
                {
                    Name = tableInfo.Name,
                    Description = tableInfo.Description,
                    ColumnInfos = columnInfos.Select(info => new DbColumnInfo
                    {
                        DbColumnName = info.DbColumnName,
                        DataType = info.DataType,
                        IsIdentity = info.IsIdentity,
                        IsPrimarykey = info.IsPrimarykey,
                        Length = info.Length,
                        ColumnDescription = info.ColumnDescription
                    }).ToList()
                });
            }

            databaseInfos.Add(databaseInfo);
        }

        return databaseInfos;
    }

    /// <summary>
    /// 获取样式
    /// </summary>
    /// <param name="workbook"></param>
    /// <returns></returns>
    private static ICellStyle GetCellHeaderStyle(IWorkbook workbook)
    {
        var font = (XSSFFont)workbook.CreateFont();
        font.IsBold = true;

        var style = (XSSFCellStyle)workbook.CreateCellStyle();
        style.SetFillForegroundColor(GetXssfColor(Color.Gray));
        style.FillPattern = FillPattern.SolidForeground;
        style.SetFont(font);
        // style.BorderBottom = BorderStyle.Thin;
        // style.BorderLeft = BorderStyle.Thin;
        // style.BorderRight = BorderStyle.Thin;
        // style.BorderTop = BorderStyle.Thin;
        return style;
    }

    /// <summary>
    /// 获取颜色
    /// </summary>
    /// <param name="color"></param>
    /// <returns></returns>
    private static XSSFColor GetXssfColor(Color color)
    {
        return new XSSFColor([color.R, color.G, color.B]);
    }
}

/// <summary>
/// 数据库信息
/// </summary>
public class DbDatabaseInfo
{
    /// <summary>
    /// 数据库ID
    /// </summary>
    public string ConfigId { get; set; }

    /// <summary>
    /// 表格列表
    /// </summary>
    public List<DbTableInfo> TableInfos { get; set; } = new();
}

/// <summary>
/// 表格信息
/// </summary>
public class DbTableInfo
{
    /// <summary>
    /// 名称
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 类型
    /// </summary>
    public string Type { get; set; } = "数据表";

    /// <summary>
    /// 描述
    /// </summary>
    public string Description { get; set; }

    /// <summary>
    /// 列
    /// </summary>
    public List<DbColumnInfo> ColumnInfos { get; set; } = new();
}

/// <summary>
/// 数据库列
/// </summary>
public class DbColumnInfo
{
    /// <summary>
    /// 列名
    /// </summary>
    public string DbColumnName { get; set; }

    /// <summary>
    /// 列描述
    /// </summary>
    public string ColumnDescription { get; set; }

    /// <summary>
    /// 数据类型
    /// </summary>
    public string DataType { get; set; }

    /// <summary>
    /// 主键
    /// </summary>
    public bool IsPrimarykey { get; set; }

    /// <summary>
    /// 自增
    /// </summary>
    public bool IsIdentity { get; set; }

    /// <summary>
    /// 长度
    /// </summary>
    public int Length { get; set; }
}